<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Bbs_model extends CI_Model {

	function __construct() {
		parent::__construct();
		
	}

	/**
	 * 글 수정
	 */
	function modify_bbs($bbs_content, $result_upload) {
		log_message('debug',"#### Bbs_model::modify_bbs($bbs_content, $result_upload)");
		
		$this->update_bbs($bbs_content);

		// 첨부파일이 있으면 기존 파일 삭제 후 파일정보 저장
		if (is_array($result_upload)) {
			$this->delete_bbs_file($bbs_content['idx']);
			$this->insert_bbs_file($bbs_content['idx'], $result_upload);
		}
	}

	/**
	 * 글 내용 업데이트
	 */
	function update_bbs($bbs_content) {
		log_message('debug',"#### Bbs_model::update_bbs($bbs_content)");

		$this->db->set('service_id', $bbs_content['service_id']);
		$this->db->set('subject'   , $bbs_content['subject']);
		$this->db->set('content'   , $bbs_content['content']);
		$this->db->set('update_dt' , 'now()', FALSE);
		$this->db->where('idx', $bbs_content['idx']);
		$this->db->update('tb_bbs') or $this->logsql->log_sql('error');

		$this->logsql->log_sql();
	}
	
	/**
	 * 글 저장
	 */
	function save_bbs($bbs_content, $result_upload) {
		log_message('debug',"#### Bbs_model::save_bbs($bbs_content, $result_upload)");
		
		$this->db->trans_start();
		
		$this->insert_bbs($bbs_content);

		if (is_array($result_upload)) {
			$idx = $this->get_bbs_idx($bbs_content['user_id']);
			$this->insert_bbs_file($idx, $result_upload);
		}
		
		$this->db->trans_complete();
	}
	
	/**
	 * 글 내용 저장
	 */
	function insert_bbs($bbs_content) {
		log_message('debug',"#### Bbs_model::insert_bbs($bbs_content)");

		$this->db->set('category'  , $bbs_content['category']);
		$this->db->set('user_id'   , $bbs_content['user_id']);
		$this->db->set('nickname'  , $bbs_content['nickname']);
		$this->db->set('service_id', $bbs_content['service_id']);
		$this->db->set('subject'   , $bbs_content['subject']);
		$this->db->set('content'   , $bbs_content['content']);
		$this->db->set('create_dt' , 'now()', FALSE);
		$this->db->set('update_dt' , 'now()', FALSE);
		$this->db->insert('tb_bbs') or $this->logsql->log_sql('error');

		$this->logsql->log_sql();
	}

	/**
	 * 파일 저장
	 */
	function insert_bbs_file($idx, $result_upload) {
		log_message('debug',"#### Bbs_model::insert_bbs_file($idx, $result_upload)");

		$this->db->set('idx'           , $idx);
		$this->db->set('file_name'     , $result_upload['file_name']);
		$this->db->set('file_type'     , $result_upload['file_type']);
		$this->db->set('file_path'     , $result_upload['file_path']);
		$this->db->set('file_url'      , $result_upload['file_url']);
		$this->db->set('orig_name'     , $result_upload['orig_name']);
		$this->db->set('file_ext'      , $result_upload['file_ext']);
		$this->db->set('file_size'     , $result_upload['file_size']);
		$this->db->set('is_image'      , $result_upload['is_image'] == 1 ? 'Y' : 'N');
		$this->db->set('image_width'   , $result_upload['image_width']);
		$this->db->set('image_height'  , $result_upload['image_height']);
		$this->db->set('image_type'    , $result_upload['image_type']);
		$this->db->set('image_size_str', $result_upload['image_size_str']);
		$this->db->insert('tb_bbs_file') or $this->logsql->log_sql('error');
		
		$this->logsql->log_sql();
	}
	
	/**
	 * 파일정보 삭제
	 */
	function delete_bbs_file($idx) {
		log_message('debug',"#### Bbs_model::delete_bbs_file($idx)");

		$filename = $this->get_bbs_file_column_value($idx, 'file_name');
		unlink(FILE_UPLOAD_DIR.$filename);

		$this->db->where('idx', $idx);
		$this->db->delete('tb_bbs_file') or $this->logsql->log_sql('error');
		
		$this->logsql->log_sql();
	}
	
	/**
	 * 파일정보 삭제
	 */
	function delete_bbs_reply($seq) {
		log_message('debug',"#### Bbs_model::delete_bbs_reply($seq)");

		$this->db->where('seq', $seq);
		$this->db->delete('tb_bbs_reply') or $this->logsql->log_sql('error');
		
		$this->logsql->log_sql();
	}
	
	/**
	 * 조회수 업데이트
	 */
	function update_bbs_hit($idx) {
		log_message('debug',"#### Bbs_model::update_bbs_hit($idx)");

		$this->db->set('hit','hit+1', FALSE);
		$this->db->where('idx', $idx);
		$this->db->update('tb_bbs') or $this->logsql->log_sql('error');
	}

	/**
	 * 글 내용 가져오기
	 */
	function get_bbs_content($cate, $idx) {
		log_message('debug',"#### Bbs_model::get_bbs_content($cate, $idx)");
		
		$this->db->select('B.idx, B.category, B.user_id, B.nickname, B.service_id, B.artist_idx,(SELECT service_name FROM tb_service WHERE service_id = B.service_id) service_name, B.subject, B.content, B.hit, B.recommend, B.notice_yn, B.best_yn, B.create_dt, B.update_dt');
		$this->db->from('tb_bbs B');
		$this->db->where('B.idx',$idx);
		$this->db->where('B.category',$cate);
		$this->db->order_by('B.idx','desc');
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		$result['file'] = $this->select_bbs_file_list($idx);
		
		return $result;
	}
	
	/**
	 * 글 내용 가져오기
	 */
	function select_bbs_file_list($idx) {
		log_message('debug',"#### Bbs_model::select_bbs_file_list($idx)");
		
		$sql  = PHP_EOL." SELECT F.* ";
		$sql .= PHP_EOL."     , CONCAT(file_url,raw_name,'_SIZE',file_ext) thumb_url ";
		$sql .= PHP_EOL." from tb_bbs_file F ";
		$sql .= PHP_EOL." where F.idx = ? ";
		$query = $this->db->query($sql,array($idx)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		return $result;
	}
	
	/**
	 * 이전,다음 글 가져오기
	 */
	function get_bbs_side_content($category, $idx, $type) {
		log_message('debug',"#### Bbs_model::get_bbs_side_content($category, $idx, $type)");
		
		$this->db->select('B.idx, B.category, B.user_id, B.nickname, B.subject, B.hit, B.recommend, B.notice_yn, B.best_yn, B.create_dt, B.update_dt, F.file_name, F.file_type, F.file_path, F.orig_name, F.file_ext, F.file_size, F.is_image, F.image_width, F.image_height, F.image_type, F.image_size_str, (SELECT COUNT(*) FROM tb_bbs_reply WHERE idx = B.idx) reply_cnt');
		$this->db->from('tb_bbs B');
		$this->db->join('tb_bbs_file F',  'B.idx = F.idx', 'left outer');
		$this->db->where('B.category',$category);
		if ($type == 'prev') {
			$this->db->where('B.idx > ',$idx);
		} elseif ($type == 'next') {
			$this->db->where('B.idx < ',$idx);
		}
		$this->db->order_by('B.idx','desc');
		$this->db->limit(1);
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		return $result;
	}
	
	/**
	 * 파일 정보를 저장하기 위해 마지막 저장 글의 idx를 가져온다.
	 */
	function get_bbs_idx($user_id) {
		log_message('debug',"#### Bbs_model::get_bbs_idx($user_id)");
		
		$this->db->select_max('idx');
		$this->db->where('user_id',$user_id);
		$query = $this->db->get('tb_bbs') or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		return $result['idx'];
	}
	
	/**
	 * 글 리스트
	 */
	function select_bbs_list($category, $best_yn, $notice_yn, $search_type, $search_keyword, $page_no, $page_size, $user_id = NULL) {
		log_message('debug',"#### Bbs_model::select_bbs_list($category, $best_yn, $notice_yn, $search_type, $search_keyword, $page_no, $page_size, $user_id )");
		
		$start_row = ($page_no-1) * $page_size;

		$sql  = PHP_EOL." SELECT  ";
		$sql .= PHP_EOL."       B.idx ";
		$sql .= PHP_EOL."     , B.category ";
		$sql .= PHP_EOL."     , B.user_id ";
		$sql .= PHP_EOL."     , B.nickname ";
		$sql .= PHP_EOL."     , B.service_id ";
		$sql .= PHP_EOL."     , (SELECT service_url FROM tb_service WHERE service_id = B.service_id) service_url ";
		$sql .= PHP_EOL."     , (SELECT service_name FROM tb_service WHERE service_id = B.service_id) service_name ";
		$sql .= PHP_EOL."     , (SELECT service_group_name FROM tb_service_group WHERE service_group_id = (SELECT service_group_id FROM tb_service WHERE service_id = B.service_id)) service_group_name ";
		$sql .= PHP_EOL."     , B.subject ";
		$sql .= PHP_EOL."     , B.content ";
		$sql .= PHP_EOL."     , B.hit ";
		$sql .= PHP_EOL."     , B.recommend ";
		$sql .= PHP_EOL."     , B.best_yn ";
		$sql .= PHP_EOL."     , B.notice_yn ";
		$sql .= PHP_EOL."     , B.secret_yn ";
		$sql .= PHP_EOL."     , B.create_dt ";
		$sql .= PHP_EOL."     , B.update_dt ";
		// $sql .= PHP_EOL."     , F.file_name ";
		// $sql .= PHP_EOL."     , F.file_type ";
		// $sql .= PHP_EOL."     , F.file_path ";
		// $sql .= PHP_EOL."     , F.orig_name ";
		// $sql .= PHP_EOL."     , F.file_ext ";
		// $sql .= PHP_EOL."     , F.file_size ";
		// $sql .= PHP_EOL."     , F.is_image ";
		// $sql .= PHP_EOL."     , F.image_width ";
		// $sql .= PHP_EOL."     , F.image_height ";
		// $sql .= PHP_EOL."     , F.image_type ";
		// $sql .= PHP_EOL."     , F.image_size_str ";
		$sql .= PHP_EOL."     , (SELECT COUNT(*) FROM tb_bbs_reply WHERE idx = B.idx) reply_cnt ";
		$sql .= PHP_EOL." FROM  tb_bbs B ";
		// $sql .= PHP_EOL." LEFT OUTER JOIN tb_bbs_file F ON B.idx = F.idx ";
		$sql .= PHP_EOL." WHERE 1=1 ";
		if (!empty($category)) {
			$sql .= PHP_EOL." AND   category = '$category' ";
		} else {
			$sql .= PHP_EOL." AND   category <> 'qna' ";
		}
		if (!empty($user_id)) {
			$sql .= PHP_EOL." AND   user_id = '$user_id' ";
		}
		if (!empty($search_keyword)) {
			$sql .= PHP_EOL." AND   B.$search_type LIKE '%$search_keyword%' ";
		}
		if ($best_yn == 'Y') {
			$sql .= PHP_EOL." AND   B.best_yn = '$best_yn' ";
		}
		$sql .= PHP_EOL." AND   B.notice_yn = '$notice_yn' ";
		$sql .= PHP_EOL." ORDER BY B.create_dt DESC ";
		$sql .= PHP_EOL." LIMIT $start_row, $page_size ";
		$query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
//		$this->logsql->log_result($result);

		for ($i=0; $i < count($result); $i++) { 
			$result[$i]['file'] = $this->select_bbs_file_list($result[$i]['idx']);
		}

		return $result;
	}

	/**
	 * 총 내역 수
	 */
	function get_bbs_total_count($category, $best_yn, $notice_yn, $search_type, $search_keyword) {
		log_message('debug',"#### Bbs_model::get_bbs_total_count($category, $best_yn, $notice_yn, $search_type, $search_keyword)");

		$this->db->where('category' , $category);
		if ($best_yn == 'Y') {
			$this->db->where('best_yn'  , $best_yn);
		}
		$this->db->where('notice_yn', $notice_yn);
		if (!empty($search_keyword)) {
			$this->db->like($search_type, $search_keyword);
		}
		$this->db->from('tb_bbs');
		$result = $this->db->count_all_results();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 댓글 등록
	 */
	function insert_bbs_reply($bbs_reply) {
		log_message('debug',"#### Bbs_model::insert_bbs_reply($bbs_reply)");
		
		$this->db->set('idx'      , $bbs_reply['idx']);
		$this->db->set('user_id'  , $bbs_reply['user_id']);
		$this->db->set('nickname' , $bbs_reply['nickname']);
		$this->db->set('content'  , $bbs_reply['content']);
		$this->db->set('create_dt', 'now()', FALSE);
		$this->db->insert('tb_bbs_reply') or $this->logsql->log_sql('error');

		$this->logsql->log_sql();
	}
	
	/**
	 * 댓글 리스트
	 */
	function select_bbs_reply_list($idx) {
		log_message('debug',"#### Bbs_model::select_bbs_reply_list($idx)");
		
		$this->db->from('tb_bbs_reply');
		$this->db->where('idx', $idx);
		$this->db->order_by('seq', 'asc');
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		return $result;
	}
	
	/**
	 * 공감하기 처리
	 */
	function update_bbs_recommend($idx, $user_id) {
		log_message('debug',"#### Bbs_model::update_bbs_recommend($idx, $user_id)");
		
		$this->db->set('recommend','recommend+1', FALSE);
		$this->db->set('recommend_user',"CONCAT(IFNULL(recommend_user,''),'$user_id,')", FALSE);
		$this->db->where('idx', $idx);
		$result = $this->db->update('tb_bbs') or $this->logsql->log_sql('error');
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		if ($result > 0) {
			return TRUE;
		}
		return FALSE;
	}

	/**
	 * 게시판 테이블에서 특정한 컬럼의 값을 가져온다.
	 */
	function get_bbs_column_value($idx, $col_name) {
		log_message('debug',"#### Bbs_model::get_bbs_column_value($idx, $col_name)");
		
		$this->db->select($col_name);
		$this->db->from('tb_bbs');
		$this->db->where('idx', $idx);
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		if (count($result) == 1) {
			return $result[$col_name];
		}
		return '';
	}
	
	/**
	 * 파일 테이블에서 특정한 컬럼의 값을 가져온다.
	 */
	function get_bbs_file_column_value($idx, $col_name) {
		log_message('debug',"#### Bbs_model::get_bbs_file_column_value($idx, $col_name)");
		
		$this->db->select($col_name);
		$this->db->from('tb_bbs_file');
		$this->db->where('idx', $idx);
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		if (count($result) == 1) {
			return $result[$col_name];
		}
		return '';
	}
	
	/**
	 * 파일 테이블에서 특정한 컬럼의 값을 가져온다.
	 */
	function get_bbs_reply_column_value($seq, $col_name) {
		log_message('debug',"#### Bbs_model::get_bbs_reply_column_value($seq, $col_name)");
		
		$this->db->select($col_name);
		$this->db->from('tb_bbs_reply');
		$this->db->where('seq', $seq);
		$query = $this->db->get() or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		if (count($result) == 1) {
			return $result[$col_name];
		}
		return '';
	}
	
	/**
	 * 글 삭제
	 * 글에 연관된 모든 데이터 삭제
	 * 1. 댓글 삭제
	 * 2. 첨부파일정보 삭제
	 * 3. 글 삭제
	 * 4. 첨부파일 삭제
	 */
	function remove_bbs($idx) {
		log_message('debug',"#### Bbs_model::remove_bbs($idx)");
		
		$this->db->trans_start();

		$filename = $this->get_bbs_file_column_value($idx, 'file_name');

		$tables = array(
			  'tb_bbs_reply'
			, 'tb_bbs_file'
			, 'tb_bbs'
		);
		$this->db->where('idx', $idx);
		$this->db->delete($tables);

		$this->db->trans_complete();
		
		
		if ($this->db->trans_status() == TRUE) {
			// db삭제가 완료되면 첨부파일 삭제
			if (!empty($filename)) {
				if(is_file(FILE_UPLOAD_DIR.$filename)){
					unlink(FILE_UPLOAD_DIR.$filename);
					log_message('debug',"## ".FILE_UPLOAD_DIR.$filename."삭제 완료");
				}
			}
			return TRUE;
		}
		return FALSE;
	}
}

/* End of file bbs_model.php */
/* Location: ./application/models/bbs/bbs_model.php */